from sqlalchemy.orm import aliased
from sqlalchemy.sql import func
from sqlalchemy import extract, and_
from dateutil.relativedelta import relativedelta
import datetime
from .models import *
from config import config
from app_db import db
from src.utils.json_format import MyJSONEncoder


# 维度表
def read_model_common_code(category_code=None, id=None):
    """

    :param category_code:
    :return:
    """
    if category_code:
        query_info = CommonCode.query.filter_by(category_code=category_code, is_valid=1)
    else:
        query_info = CommonCode.query.filter_by(is_valid=1)
        
    if id:
        query_info = query_info.filter(CommonCode.id != id)

    table_info = query_info.all()
    if table_info:
        return [{
            'id': data.id,
            'name': data.name
        } for data in table_info]


# 收支类别
def read_model_income_expense_class(type_id=1, parent_id=None):
    """

    :param type_id:
    :param parent_id:
    :return:
    """
    query_info = IncomeExpenseClass.query.filter_by(type_id=type_id)
    if parent_id == 0:
        query_info = query_info.filter(IncomeExpenseClass.parent_id.isnot(None))
    else:
        query_info = query_info.filter_by(parent_id=parent_id)
    query_info = query_info.filter_by(is_valid=1).all()
    if query_info:
        return [{
            'id': data.id,
            'parent_id': data.parent_id,
            'type_name': data.type_name
        } for data in query_info]
    return []


# 账户表
def read_model_account_type(user_id):
    """

    :param user_id:
    :return:
    """
    query_info = AccountType.query.join(CommonCode, CommonCode.id == AccountType.type_id).\
        add_columns(AccountType.id, CommonCode.name, AccountType.type_id, AccountType.account_name).\
        filter(AccountType.user_id == user_id).all()
    if query_info:
        return [{
            'id': data.id,
            'name': data.name,
            'type_id': data.type_id,
            'account_name': data.account_name
        } for data in query_info]
    return []


# 流水账表
def read_account_info(user_id, date=None, money=None, income_expense_id=None, account_id=None):
    """

    :param user_id:
    :return:
    """
    IncomeExpenseClass_BIG = aliased(IncomeExpenseClass)
    CommonCode_PayChannel = aliased(CommonCode)
    
    if date and money and income_expense_id and account_id:
        query = AccountInfo.query.filter_by(user_id=user_id).filter_by(date=date).filter_by(money=money).\
            filter_by(income_expense_id=income_expense_id).filter_by(account_id=account_id).\
            filter_by(is_valid=1).first()
        return True if query else False

    return AccountInfo.query.join(IncomeExpenseClass, IncomeExpenseClass.id == AccountInfo.income_expense_id).\
        join(IncomeExpenseClass_BIG, IncomeExpenseClass_BIG.id == AccountInfo.income_expense_class_id).\
        join(AccountType, AccountType.id == AccountInfo.account_id).\
        join(CommonCode, CommonCode.id == AccountInfo.account_class_id).\
        join(CommonCode_PayChannel, CommonCode_PayChannel.id == AccountInfo.pay_channel_id).\
        add_columns(AccountInfo.date, AccountInfo.money, AccountInfo.income_expense_type_id,
                    IncomeExpenseClass.type_name, AccountType.account_name, AccountInfo.remark, CommonCode.name,
                    IncomeExpenseClass_BIG.type_name, CommonCode_PayChannel.name.label('pay_channel_name'), AccountInfo.id).\
        filter(AccountInfo.user_id == user_id).filter(AccountInfo.is_valid==1).order_by(AccountInfo.date.desc())


# 历史流水账表
def read_account_history(user_id, date, date_type, income_expense_id=None, income_expense_class_id=None,
                         income_expense_type_id=None, account_class_id=None, account_id=None, pay_channel_id=None):
    """

    :param user_id:
    :return:
    """
    IncomeExpenseClass_BIG = aliased(IncomeExpenseClass)
    CommonCode_PayChannel = aliased(CommonCode)
    
    query = AccountInfo.query.join(IncomeExpenseClass, IncomeExpenseClass.id == AccountInfo.income_expense_id). \
        join(IncomeExpenseClass_BIG, IncomeExpenseClass_BIG.id == AccountInfo.income_expense_class_id). \
        join(AccountType, AccountType.id == AccountInfo.account_id). \
        join(CommonCode, CommonCode.id == AccountInfo.account_class_id). \
        join(CommonCode_PayChannel, CommonCode_PayChannel.id == AccountInfo.pay_channel_id).\
        add_columns(AccountInfo.date, AccountInfo.money, AccountInfo.income_expense_type_id,
                    IncomeExpenseClass.type_name, AccountType.account_name, AccountInfo.remark, CommonCode.name,
                    IncomeExpenseClass_BIG.type_name, CommonCode_PayChannel.name.label('pay_channel_name'), AccountInfo.id).\
        filter(AccountInfo.user_id == user_id).filter(AccountInfo.is_valid==1)

    year = date[:4]
    month = date[5:7]
    day = date[8:]
    query = query.filter(extract('year', AccountInfo.date) == year)
    if date_type == 2:
        query = query.filter(extract('month', AccountInfo.date) == month)
    if date_type == 1:
        query = query.filter(extract('day', AccountInfo.date) == day)

    # if date_type == 1:
    #     query = query.filter(AccountInfo.date == date)
    # if date_type == 2:
    #     month = date[:7]
    #     query = query.filter(AccountInfo.date.like('{}%'.format(month)))
    # if date_type == 3:
    #     year = date[:4]
    #     query = query.filter(AccountInfo.date.like('{}%'.format(year)))

    if income_expense_id:
        query = query.filter(AccountInfo.income_expense_id == income_expense_id)
    if income_expense_class_id:
        query = query.filter(AccountInfo.income_expense_class_id == income_expense_class_id)
    if income_expense_type_id:
        query = query.filter(AccountInfo.income_expense_type_id == income_expense_type_id)
    if pay_channel_id:
        query = query.filter(AccountInfo.pay_channel_id == pay_channel_id)
    if account_class_id:
        query = query.filter(AccountInfo.account_class_id == account_class_id)
    if account_id:
        query = query.filter(AccountInfo.account_id == account_id)
 
    return query.order_by(AccountInfo.date.desc())


# 最近12个月的还款金额趋势
def repayment_recenty(user_id, begin_year=None, begin_month=None, end_year=None, end_month=None, account_id=None):
    """

    :param user_id:
    :return:
    """
    if not end_year:
        end = datetime.datetime.now()
        end_year = end.year
        end_month = end.month

    next_month = datetime.datetime.strptime('{}-{}-01'.format(end_year, end_month), '%Y-%m-%d')
    next_month = next_month + relativedelta(months=1)
    end_day = next_month + datetime.timedelta(days=-1)
    # end_day = end_day.strftime('%Y-%m-%d')

    if not begin_year:
        begin = datetime.datetime.now() + relativedelta(months=-12)
        begin_year = begin.year
        begin_month = begin.month

    begin_day = '{}-{}-01'.format(begin_year, begin_month)
    begin_day = datetime.datetime.strptime(begin_day, '%Y-%m-%d')

    if account_id:
        query = db.session.query(func.sum(AccountInfo.money).label("money"),
                                 func.substr(AccountInfo.date, 1, 7).label("month")). \
            filter(AccountInfo.user_id == user_id).filter(AccountInfo.income_expense_type_id == 1). \
            filter(AccountInfo.income_expense_id == 1909).filter(AccountInfo.is_valid == 1). \
            filter(AccountInfo.date.between(begin_day, end_day)).filter(AccountInfo.account_id == account_id). \
            group_by(func.substr(AccountInfo.date, 1, 7)).order_by(func.substr(AccountInfo.date, 1, 7)).all()
    else:
        query = db.session.query(func.sum(AccountInfo.money).label("money"),
                             func.substr(AccountInfo.date, 1, 7).label("month")). \
        filter(AccountInfo.user_id == user_id).filter(AccountInfo.income_expense_type_id == 1). \
        filter(AccountInfo.income_expense_id == 1909).filter(AccountInfo.is_valid == 1).\
        filter(AccountInfo.date.between(begin_day, end_day)). \
        group_by(func.substr(AccountInfo.date, 1, 7)).order_by(func.substr(AccountInfo.date, 1, 7)).all()

    # query = db.session.query(func.sum(AccountInfo.money).label("money"), func.substr(AccountInfo.date, 1, 7).label("month")).\
    #     filter(AccountInfo.user_id == user_id).filter(AccountInfo.income_expense_type_id == 1).\
    #     filter(AccountInfo.income_expense_id == 1909).filter(AccountInfo.is_valid == 1).\
    #     group_by(func.substr(AccountInfo.date, 1, 7)).order_by(func.substr(AccountInfo.date, 1, 7).desc()).limit(12).all()

    if query:
        return [{
            'money': str(data.money),
            'month': data.month
        } for data in query]
    return []


# 剔除还信用卡后的近30日消费趋势
def cost_recenty(user_id, income_expense_class_id=None, income_expense_id=None, pay_channel_id=None,
                 account_class_id=None, account_id=None, begin_year=None, begin_month=None, begin_day=None,
                 end_year=None, end_month=None, end_day=None):
    """

    :param user_id:
    :return:
    """
    begin = end = '0-0-0'
    end_datetime = datetime.datetime.now()
    if not end_year and not begin_year:
        # 默认，最近30个消费日
        flag = 'D'
        end = '{}-{}-{}'.format(end_datetime.year, end_datetime.month, end_datetime.day)
        begin = end_datetime + datetime.timedelta(days=-30)
        begin = '{}-{}-{}'.format(begin.year, begin.month, begin.day)

    if (end_year and not end_month) or (begin_year and not begin_month):
        flag = 'Y'
        if end_year and begin_year:
            begin = '{}-01-01'.format(begin_year)
            end = '{}-12-31'.format(end_year)
        if end_year and not begin_year:
            begin = '{}-01-01'.format(end_year)
            end = '{}-12-31'.format(end_year)
        if not end_year and begin_year:
            begin = '{}-01-01'.format(begin_year)
            end = '{}-12-31'.format(begin_year)

    if (end_year and end_month and not end_day) or (begin_year and begin_month and not begin_day):
        flag = 'M'
        if end_year and end_month and begin_year and begin_month:
            begin = '{}-{}-01'.format(begin_year, begin_month)
            if end_month != 12:
                end_next_day = '{}-{}-01'.format(end_year, end_month + 1)
                end = datetime.datetime.strptime(end_next_day, '%Y-%m-%d') + datetime.timedelta(days=-1)
                end = end.strftime('%Y-%m-%d')
            else:
                end = '{}-12-31'.format(end_year)
        if end_year and end_month and begin_year and not begin_month:
            begin = '{}-01-01'.format(begin_year)
            if end_month != 12:
                end_next_day = '{}-{}-01'.format(end_year, end_month + 1)
                end = datetime.datetime.strptime(end_next_day, '%Y-%m-%d') + datetime.timedelta(days=-1)
                end = end.strftime('%Y-%m-%d')
            else:
                end = '{}-12-31'.format(end_year)
        if end_year and end_month and not begin_year and not begin_month:
            begin = '{}-{}-01'.format(end_year, end_month)
            if end_month != 12:
                end_next_day = '{}-{}-01'.format(end_year, end_month + 1)
                end = datetime.datetime.strptime(end_next_day, '%Y-%m-%d') + datetime.timedelta(days=-1)
                end = end.strftime('%Y-%m-%d')
            else:
                end = '{}-12-31'.format(end_year)
        if end_year and not end_month and begin_year and begin_month:
            begin = '{}-{}-01'.format(begin_year, begin_month)
            end = '{}-12-31'.format(end_year)
        if not end_year and not end_month and begin_year and begin_month:
            begin = '{}-{}-01'.format(begin_year, begin_month)
            end = '{}-{}-{}'.format(end_datetime.year, end_datetime.month, end_datetime.day)

    if begin_year and begin_month and begin_day:
        flag = 'D'
        begin = '{}-{}-{}'.format(begin_year, begin_month, begin_day)
        if end_year and not end_month:
            end = '{}-12-31'.format(end_year)
        if end_year and end_month and not end_day:
            if end_month != 12:
                end_next_day = '{}-{}-01'.format(end_year, end_month + 1)
                end = datetime.datetime.strptime(end_next_day, '%Y-%m-%d') + datetime.timedelta(days=-1)
                end = end.strftime('%Y-%m-%d')
            else:
                end = '{}-12-31'.format(end_year)
        if end_year and end_month and end_day:
            end = '{}-{}-{}'.format(end_year, end_month, end_day)

    if begin == '0-0-0' or end == '0-0-0':
        return None

    if flag == 'D':
        query = db.session.query(func.sum(AccountInfo.money).label("money"), AccountInfo.date.label("date")). \
            filter(AccountInfo.user_id == user_id).filter(AccountInfo.income_expense_type_id == 1). \
            filter(AccountInfo.income_expense_id != 1909).filter(AccountInfo.is_valid == 1). \
            filter(AccountInfo.date.between(begin, end))
        if account_id:
            query = query.filter(AccountInfo.account_id == account_id)
        if income_expense_class_id:
            query = query.filter(AccountInfo.income_expense_class_id == income_expense_class_id)
        if income_expense_id:
            query = query.filter(AccountInfo.income_expense_id == income_expense_id)
        if pay_channel_id:
            query = query.filter(AccountInfo.pay_channel_id == pay_channel_id)
        if account_class_id:
            query = query.filter(AccountInfo.account_class_id == account_class_id)

        query = query.group_by(AccountInfo.date).order_by(AccountInfo.date).all()

    if flag == 'M':
        query = db.session.query(func.sum(AccountInfo.money).label("money"), func.substr(AccountInfo.date, 1, 7).label("date")). \
            filter(AccountInfo.user_id == user_id).filter(AccountInfo.income_expense_type_id == 1). \
            filter(AccountInfo.income_expense_id != 1909).filter(AccountInfo.is_valid == 1). \
            filter(AccountInfo.date.between(begin, end))
        if account_id:
            query = query.filter(AccountInfo.account_id == account_id)
        if income_expense_class_id:
            query = query.filter(AccountInfo.income_expense_class_id == income_expense_class_id)
        if income_expense_id:
            query = query.filter(AccountInfo.income_expense_id == income_expense_id)
        if pay_channel_id:
            query = query.filter(AccountInfo.pay_channel_id == pay_channel_id)
        if account_class_id:
            query = query.filter(AccountInfo.account_class_id == account_class_id)

        query = query.group_by(func.substr(AccountInfo.date, 1, 7)).order_by(func.substr(AccountInfo.date, 1, 7)).all()

    if flag == 'Y':
        query = db.session.query(func.sum(AccountInfo.money).label("money"), func.substr(AccountInfo.date, 1, 4).label("date")). \
            filter(AccountInfo.user_id == user_id).filter(AccountInfo.income_expense_type_id == 1). \
            filter(AccountInfo.income_expense_id != 1909).filter(AccountInfo.is_valid == 1). \
            filter(AccountInfo.date.between(begin, end))
        if account_id:
            query = query.filter(AccountInfo.account_id == account_id)
        if income_expense_class_id:
            query = query.filter(AccountInfo.income_expense_class_id == income_expense_class_id)
        if income_expense_id:
            query = query.filter(AccountInfo.income_expense_id == income_expense_id)
        if pay_channel_id:
            query = query.filter(AccountInfo.pay_channel_id == pay_channel_id)
        if account_class_id:
            query = query.filter(AccountInfo.account_class_id == account_class_id)

        query = query.group_by(func.substr(AccountInfo.date, 1, 4)).order_by(func.substr(AccountInfo.date, 1, 4)).all()

    # query = db.session.query(func.sum(AccountInfo.money).label("money"), AccountInfo.date).\
    #     filter(AccountInfo.user_id == user_id).filter(AccountInfo.income_expense_type_id == 1).\
    #     filter(AccountInfo.income_expense_id != 1909).filter(AccountInfo.is_valid == 1).\
    #     group_by(AccountInfo.date).order_by(AccountInfo.date.desc()).limit(30).all()
    print('flag: {}'.format(flag))
    if query:
        return [{
            'money': str(data.money),
            'date': str(data.date)
        } for data in query]
    return []


# 剔除还信用卡后的消费结构
def cost_recenty_struct_class(user_id, pay_channel_id=None,
                 account_class_id=None, account_id=None, begin_year=None, begin_month=None, begin_day=None,
                 end_year=None, end_month=None, end_day=None):
    """

    :param user_id:
    :return:
    """
    begin = end = '0-0-0'
    end_datetime = datetime.datetime.now()
    if not end_year and not begin_year:
        # 默认，最近30个消费日
        flag = 'D'
        end = '{}-{}-{}'.format(end_datetime.year, end_datetime.month, end_datetime.day)
        begin = end_datetime + datetime.timedelta(days=-30)
        begin = '{}-{}-{}'.format(begin.year, begin.month, begin.day)

    if (end_year and not end_month) or (begin_year and not begin_month):
        if end_year and begin_year:
            begin = '{}-01-01'.format(begin_year)
            end = '{}-12-31'.format(end_year)
        if end_year and not begin_year:
            begin = '{}-01-01'.format(end_year)
            end = '{}-12-31'.format(end_year)
        if not end_year and begin_year:
            begin = '{}-01-01'.format(begin_year)
            end = '{}-12-31'.format(begin_year)

    if (end_year and end_month and not end_day) or (begin_year and begin_month and not begin_day):
        if end_year and end_month and begin_year and begin_month:
            begin = '{}-{}-01'.format(begin_year, begin_month)
            if end_month != 12:
                end_next_day = '{}-{}-01'.format(end_year, end_month + 1)
                end = datetime.datetime.strptime(end_next_day, '%Y-%m-%d') + datetime.timedelta(days=-1)
                end = end.strftime('%Y-%m-%d')
            else:
                end = '{}-12-31'.format(end_year)
        if end_year and end_month and begin_year and not begin_month:
            begin = '{}-01-01'.format(begin_year)
            if end_month != 12:
                end_next_day = '{}-{}-01'.format(end_year, end_month + 1)
                end = datetime.datetime.strptime(end_next_day, '%Y-%m-%d') + datetime.timedelta(days=-1)
                end = end.strftime('%Y-%m-%d')
            else:
                end = '{}-12-31'.format(end_year)
        if end_year and end_month and not begin_year and not begin_month:
            begin = '{}-{}-01'.format(end_year, end_month)
            if end_month != 12:
                end_next_day = '{}-{}-01'.format(end_year, end_month + 1)
                end = datetime.datetime.strptime(end_next_day, '%Y-%m-%d') + datetime.timedelta(days=-1)
                end = end.strftime('%Y-%m-%d')
            else:
                end = '{}-12-31'.format(end_year)
        if end_year and not end_month and begin_year and begin_month:
            begin = '{}-{}-01'.format(begin_year, begin_month)
            end = '{}-12-31'.format(end_year)
        if not end_year and not end_month and begin_year and begin_month:
            begin = '{}-{}-01'.format(begin_year, begin_month)
            end = '{}-{}-{}'.format(end_datetime.year, end_datetime.month, end_datetime.day)

    if begin_year and begin_month and begin_day:
        begin = '{}-{}-{}'.format(begin_year, begin_month, begin_day)
        if end_year and not end_month:
            end = '{}-12-31'.format(end_year)
        if end_year and end_month and not end_day:
            if end_month != 12:
                end_next_day = '{}-{}-01'.format(end_year, end_month + 1)
                end = datetime.datetime.strptime(end_next_day, '%Y-%m-%d') + datetime.timedelta(days=-1)
                end = end.strftime('%Y-%m-%d')
            else:
                end = '{}-12-31'.format(end_year)
        if end_year and end_month and end_day:
            end = '{}-{}-{}'.format(end_year, end_month, end_day)

    if begin == '0-0-0' or end == '0-0-0':
        return None

    query = db.session.query(func.sum(AccountInfo.money).label("money"), IncomeExpenseClass.type_name.label("name")).\
        join(IncomeExpenseClass, IncomeExpenseClass.id == AccountInfo.income_expense_class_id). \
        filter(AccountInfo.user_id == user_id).filter(AccountInfo.income_expense_type_id == 1). \
        filter(AccountInfo.income_expense_id != 1909).filter(AccountInfo.is_valid == 1). \
        filter(AccountInfo.date.between(begin, end))

    query_class = db.session.query(func.sum(AccountInfo.money).label("money"), IncomeExpenseClass.parent_id,
                                   IncomeExpenseClass.type_name.label("name")). \
        join(IncomeExpenseClass, IncomeExpenseClass.id == AccountInfo.income_expense_id). \
        filter(AccountInfo.user_id == user_id).filter(AccountInfo.income_expense_type_id == 1). \
        filter(AccountInfo.income_expense_id != 1909).filter(AccountInfo.is_valid == 1). \
        filter(AccountInfo.date.between(begin, end))

    if account_id:
        query = query.filter(AccountInfo.account_id == account_id)
        query_class = query_class.filter(AccountInfo.account_id == account_id)
    if pay_channel_id:
        query = query.filter(AccountInfo.pay_channel_id == pay_channel_id)
        query_class = query_class.filter(AccountInfo.pay_channel_id == pay_channel_id)
    if account_class_id:
        query = query.filter(AccountInfo.account_class_id == account_class_id)
        query_class = query_class.filter(AccountInfo.account_class_id == account_class_id)

    query = query.group_by(IncomeExpenseClass.type_name).all()
    query_class = query_class.group_by(IncomeExpenseClass.type_name, IncomeExpenseClass.parent_id).all()

    if query:
        big_info = [{
            'value': str(data.money),
            'name': data.name
        } for data in query]

        class_info = dict()
        for info in query_class:
            money = str(info.money)
            name = info.name
            parent_id = info.parent_id
            mid_dict = {
                'value': money,
                'name': name
            }
            if class_info.get(parent_id):
                class_info[parent_id].append(mid_dict)
            else:
                class_info[parent_id] = [mid_dict]

        return [big_info, class_info]
    return [[], []]


# 剔除还信用卡后的消费结构
def cost_recenty_struct(user_id, income_expense_class_id=None, pay_channel_id=None,
                 account_class_id=None, account_id=None, begin_year=None, begin_month=None, begin_day=None,
                 end_year=None, end_month=None, end_day=None):
    """

    :param user_id:
    :return:
    """
    begin = end = '0-0-0'
    end_datetime = datetime.datetime.now()
    if not end_year and not begin_year:
        # 默认，最近30个消费日
        flag = 'D'
        end = '{}-{}-{}'.format(end_datetime.year, end_datetime.month, end_datetime.day)
        begin = end_datetime + datetime.timedelta(days=-30)
        begin = '{}-{}-{}'.format(begin.year, begin.month, begin.day)

    if (end_year and not end_month) or (begin_year and not begin_month):
        if end_year and begin_year:
            begin = '{}-01-01'.format(begin_year)
            end = '{}-12-31'.format(end_year)
        if end_year and not begin_year:
            begin = '{}-01-01'.format(end_year)
            end = '{}-12-31'.format(end_year)
        if not end_year and begin_year:
            begin = '{}-01-01'.format(begin_year)
            end = '{}-12-31'.format(begin_year)

    if (end_year and end_month and not end_day) or (begin_year and begin_month and not begin_day):
        if end_year and end_month and begin_year and begin_month:
            begin = '{}-{}-01'.format(begin_year, begin_month)
            if end_month != 12:
                end_next_day = '{}-{}-01'.format(end_year, end_month + 1)
                end = datetime.datetime.strptime(end_next_day, '%Y-%m-%d') + datetime.timedelta(days=-1)
                end = end.strftime('%Y-%m-%d')
            else:
                end = '{}-12-31'.format(end_year)
        if end_year and end_month and begin_year and not begin_month:
            begin = '{}-01-01'.format(begin_year)
            if end_month != 12:
                end_next_day = '{}-{}-01'.format(end_year, end_month + 1)
                end = datetime.datetime.strptime(end_next_day, '%Y-%m-%d') + datetime.timedelta(days=-1)
                end = end.strftime('%Y-%m-%d')
            else:
                end = '{}-12-31'.format(end_year)
        if end_year and end_month and not begin_year and not begin_month:
            begin = '{}-{}-01'.format(end_year, end_month)
            if end_month != 12:
                end_next_day = '{}-{}-01'.format(end_year, end_month + 1)
                end = datetime.datetime.strptime(end_next_day, '%Y-%m-%d') + datetime.timedelta(days=-1)
                end = end.strftime('%Y-%m-%d')
            else:
                end = '{}-12-31'.format(end_year)
        if end_year and not end_month and begin_year and begin_month:
            begin = '{}-{}-01'.format(begin_year, begin_month)
            end = '{}-12-31'.format(end_year)
        if not end_year and not end_month and begin_year and begin_month:
            begin = '{}-{}-01'.format(begin_year, begin_month)
            end = '{}-{}-{}'.format(end_datetime.year, end_datetime.month, end_datetime.day)

    if begin_year and begin_month and begin_day:
        begin = '{}-{}-{}'.format(begin_year, begin_month, begin_day)
        if end_year and not end_month:
            end = '{}-12-31'.format(end_year)
        if end_year and end_month and not end_day:
            if end_month != 12:
                end_next_day = '{}-{}-01'.format(end_year, end_month + 1)
                end = datetime.datetime.strptime(end_next_day, '%Y-%m-%d') + datetime.timedelta(days=-1)
                end = end.strftime('%Y-%m-%d')
            else:
                end = '{}-12-31'.format(end_year)
        if end_year and end_month and end_day:
            end = '{}-{}-{}'.format(end_year, end_month, end_day)

    if begin == '0-0-0' or end == '0-0-0':
        return None

    if income_expense_class_id:
        query = db.session.query(func.sum(AccountInfo.money).label("money"), IncomeExpenseClass.type_name.label("name")).\
            join(IncomeExpenseClass, IncomeExpenseClass.id == AccountInfo.income_expense_id). \
            filter(AccountInfo.user_id == user_id).filter(AccountInfo.income_expense_type_id == 1). \
            filter(AccountInfo.income_expense_id != 1909).filter(AccountInfo.is_valid == 1).\
            filter(AccountInfo.income_expense_class_id == income_expense_class_id). \
            filter(AccountInfo.date.between(begin, end))

        if account_id:
            query = query.filter(AccountInfo.account_id == account_id)
        if pay_channel_id:
            query = query.filter(AccountInfo.pay_channel_id == pay_channel_id)
        if account_class_id:
            query = query.filter(AccountInfo.account_class_id == account_class_id)

        query = query.group_by(IncomeExpenseClass.type_name).all()
    else:
        query = db.session.query(func.sum(AccountInfo.money).label("money"), IncomeExpenseClass.type_name.label("name")).\
            join(IncomeExpenseClass, IncomeExpenseClass.id == AccountInfo.income_expense_id). \
            filter(AccountInfo.user_id == user_id).filter(AccountInfo.income_expense_type_id == 1). \
            filter(AccountInfo.income_expense_id != 1909).filter(AccountInfo.is_valid == 1). \
            filter(AccountInfo.date.between(begin, end))

        if account_id:
            query = query.filter(AccountInfo.account_id == account_id)
        if pay_channel_id:
            query = query.filter(AccountInfo.pay_channel_id == pay_channel_id)
        if account_class_id:
            query = query.filter(AccountInfo.account_class_id == account_class_id)

        query = query.group_by(IncomeExpenseClass.type_name).all()

    if query:
        return [{
            'money': str(data.money),
            'name': str(data.name)
        } for data in query]
    return []
